﻿using NDK.Module.Model;
using NDK.Utils;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;

namespace NDK.Module.DAL
{
    class HistoryDataServices
    {


        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from HistoryData");
            strSql.Append(" where ID=@ID ");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@ID", DbType.Int32,4)};
            parameters[0].Value = ID;

            return SQLiteUtils.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(HistoryDataModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into HistoryData(");
            strSql.Append("Time,SizeID,Barcode,ProductBatche,Judgement,Value)");
            strSql.Append(" values (");
            strSql.Append("@Time,@SizeID,@Barcode,@ProductBatche,@Judgement,@Value)");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@Time", DbType.DateTime),
                    new SQLiteParameter("@SizeID", DbType.String,50),
                    new SQLiteParameter("@Barcode", DbType.String),
                    new SQLiteParameter("@ProductBatche", DbType.String),
                    new SQLiteParameter("@Judgement", DbType.String),
                    new SQLiteParameter("@Value", DbType.Decimal,8)};
            parameters[0].Value = model.Time;
            parameters[1].Value = model.SizeID;
            parameters[2].Value = model.Barcode;
            parameters[3].Value = model.ProductBatche;
            parameters[4].Value = model.Judgement;
            parameters[5].Value = model.Value;

            //SQLiteUtils.connectionString = "Data Source=" + Environment.CurrentDirectory + "\\HistoryData.sqlite;password=123456";
            SQLiteUtils.ExecuteSql(strSql.ToString(), parameters);
            //SQLiteUtils.connectionString = "Data Source=" + Environment.CurrentDirectory + "\\test1.db;password=123456";
        }
        /// <summary>
        /// 增加一组数据
        /// </summary>
        public void AddList(List<HistoryDataModel> modelList)
        {
            Hashtable SQLStringList = new Hashtable();
            int i = 0;

            foreach (HistoryDataModel model in modelList)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into HistoryData(");
                strSql.Append("Time,SizeID,Barcode,ProductBatche,Judgement,Value)");
                strSql.Append(" values (");
                strSql.Append("@Time,@SizeID,@Barcode,@ProductBatche,@Judgement,@Value)");
                SQLiteParameter[] parameters = {
                            new SQLiteParameter("@Time", DbType.DateTime),
                            new SQLiteParameter("@SizeID", DbType.String,50),
                            new SQLiteParameter("@Barcode", DbType.String),
                            new SQLiteParameter("@ProductBatche", DbType.String),
                            new SQLiteParameter("@Judgement", DbType.String),
                            new SQLiteParameter("@Value", DbType.Decimal,8)};
                parameters[0].Value = model.Time;
                parameters[1].Value = model.SizeID;
                parameters[2].Value = model.Barcode;
                parameters[3].Value = model.ProductBatche;
                parameters[4].Value = model.Judgement;
                parameters[5].Value = model.Value;


                SQLStringList.Add(strSql + "_" + i++, parameters);

            }
            SQLiteUtils.ExecuteSqlTran(SQLStringList);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(HistoryDataModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update HistoryData set ");
            strSql.Append("Time=@Time,");
            strSql.Append("SizeID=@SizeID,");
            strSql.Append("Barcode=@Barcode,");
            strSql.Append("ProductBatche=@ProductBatche,");
            strSql.Append("Judgement=@Judgement,");
            strSql.Append("Value=@Value");
            strSql.Append(" where ID=@ID");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@Time", DbType.DateTime),
                    new SQLiteParameter("@SizeID", DbType.String,50),
                    new SQLiteParameter("@Barcode", DbType.String),
                    new SQLiteParameter("@ProductBatche", DbType.String),
                    new SQLiteParameter("@Judgement", DbType.String),
                    new SQLiteParameter("@Value", DbType.Decimal,8),
                    new SQLiteParameter("@ID", DbType.Int32,4)};
            parameters[0].Value = model.Time;
            parameters[1].Value = model.SizeID;
            parameters[2].Value = model.Barcode;
            parameters[3].Value = model.ProductBatche;
            parameters[4].Value = model.Judgement;
            parameters[5].Value = model.Value;
            parameters[6].Value = model.ID;

            int rows = SQLiteUtils.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from HistoryData ");
            strSql.Append(" where ID=@ID");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@ID", DbType.Int32,4)
};
            parameters[0].Value = ID;

            int rows = SQLiteUtils.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from HistoryData ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = SQLiteUtils.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HistoryDataModel GetModel(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Time,SizeID,Barcode,ProductBatche,Judgement,Value from HistoryData ");
            strSql.Append(" where ID=@ID");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@ID", DbType.Int32,4)
};
            parameters[0].Value = ID;

            HistoryDataModel model = new HistoryDataModel();
            DataSet ds = SQLiteUtils.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = ds.Tables[0].Rows[0]["ID"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Time"].ToString() != "")
                {
                    model.Time = DateTime.Parse(ds.Tables[0].Rows[0]["Time"].ToString());
                }
                model.SizeID = ds.Tables[0].Rows[0]["SizeID"].ToString();
                model.Barcode = ds.Tables[0].Rows[0]["Barcode"].ToString();
                model.ProductBatche = ds.Tables[0].Rows[0]["ProductBatche"].ToString();
                model.Judgement = ds.Tables[0].Rows[0]["Judgement"].ToString();
                if (ds.Tables[0].Rows[0]["Value"].ToString() != "")
                {
                    model.Value = decimal.Parse(ds.Tables[0].Rows[0]["Value"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Time,SizeID,Barcode,ProductBatche,Judgement,Value ");
            strSql.Append(" FROM HistoryData ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLiteUtils.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@tblName", DbType.VarChar, 255),
                    new SQLiteParameter("@fldName", DbType.VarChar, 255),
                    new SQLiteParameter("@PageSize", DbType.Int32),
                    new SQLiteParameter("@PageIndex", DbType.Int32),
                    new SQLiteParameter("@IsReCount", DbType.bit),
                    new SQLiteParameter("@OrderType", DbType.bit),
                    new SQLiteParameter("@strWhere", DbType.VarChar,1000),
                    };
            parameters[0].Value = "HistoryData";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  Method

        internal DataSet GetSpecifiedListByTimeAndSN(int sizeID, string startTime, string endTime, string SN, string Result)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  *");
            try
            {
                strSql.Append(" FROM HistoryData ");
                //SELECT TOP 20 * FROM TABLE WHERE TIME BETWEEN '2011-04-06 09:00' AND '2011-04-21 10:00'
                strSql.Append(" WHERE TIME BETWEEN  datetime('" + startTime + "') AND datetime('" + endTime + "')");
                strSql.Append(" and  SizeID = '" + sizeID + "'");
                if (SN != "")
                {
                    strSql.Append(" and  Barcode = '" + SN + "'");
                }
                if (Result != "")
                {
                    strSql.Append(" and  JugeResult = '" + Result + "'");
                }
            }
            catch { }


            return SQLiteUtils.Query(strSql.ToString());
        }
        internal DataSet GetSpecifiedListByTimeAndSNAndProject(int sizeID, string startTime, string endTime, string SN, string Project, string Result)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  *");
            try
            {
                strSql.Append(" FROM HistoryData ");
                //SELECT TOP 20 * FROM TABLE WHERE TIME BETWEEN '2011-04-06 09:00' AND '2011-04-21 10:00'
                strSql.Append(" WHERE TIME BETWEEN  datetime('" + startTime + "') AND datetime('" + endTime + "')");
                strSql.Append(" and  SizeID = '" + sizeID + "'");
                if (SN != "")
                {
                    strSql.Append(" and  Barcode = '" + SN + "'");
                }
                if (Project != "")
                {
                    strSql.Append(" and  ProductBatche = '" + Project + "'");
                }
                if (Result != "")
                {
                    strSql.Append(" and  JugeResult = '" + Result + "'");
                }
            }
            catch { }


            return SQLiteUtils.Query(strSql.ToString());
        }

        /// <summary>
        /// 删除超过时间time的数据
        /// </summary>
        public bool DeleteBeforeTime(DateTime time)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from HistoryData ");
            strSql.Append(" where Time<@time ");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@time", DbType.DateTime)};
            parameters[0].Value = time;
            int rows = SQLiteUtils.ExecuteSql(strSql.ToString(), parameters);

            System.Threading.Thread.Sleep(2000);
            strSql = new StringBuilder();
            strSql.Append("vacuum ");
            rows = SQLiteUtils.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
